﻿CREATE PROCEDURE [dbo].[proc_Assets_Getlist_G]
	(
		@CompanyId VARCHAR(500),
		@CategoryId int,
		@NameId int,
		@StateId int,
		@StartIndex int,
		@EndIndex INT,
		@DepartmentId INT,
		@UserName NVARCHAR(100),
		@bz int,
		@typeid int
		
	)
AS
Declare @str VARCHAR(500),@strsql VARCHAR(5000)
SET @str=''
IF @CompanyId <>'' AND @CompanyId <>'0'
BEGIN
	SET @str='  [CompanyId] in ('+@CompanyId+') and '
END

IF @DepartmentId <>0
BEGIN
	Declare @bmbh_T  varchar(20)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId
	--(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)
	--SET @str=@str+' DepartmentId='+convert(varchar,@DepartmentId)+' and '
	SET @str=@str+' DepartmentId in (Select Id From department Where Left(bmbh,Len('''+@bmbh_T+'''))='''+@bmbh_T+''') and '
END

IF @UserName<>''
BEGIN
	SET @str=@str+'  (EName like ''%'+@UserName+'%'' or ANumber like ''%'+@UserName+'%'') and '
END

IF @CategoryId <>0
BEGIN
	SET @str=@str+'  CategoryId='+convert(varchar,@CategoryId)+' and '
END

IF @NameId <>0
BEGIN
	SET @str=@str+'  NameId='+convert(varchar,@NameId)+' and '
END

IF @StateId <>0
BEGIN
	SET @str=@str+'  StateId='+convert(varchar,@StateId)+' and '
END

IF @typeid <>0
BEGIN
	SET @str=@str+'  typeid='+convert(varchar,@typeid)+' and '
END

IF @str <>''
BEGIN
	SET @str=' where '+substring(@str,0,len(@str)-3)
END


SET @strsql='
	;WITH list as (Select ROW_NUMBER() OVER (ORDER BY ANumber desc)AS Row,
	a.Id,
	a.GId,
	a.CategoryId,
	(Select Title From Assets_Categorys Where Id=a.CategoryId) As CategoryName,
	NameId,
	(Select Title From Assets_Names Where Id=a.NameId) As NameTitle,
	Description,
	CompanyId,
	(Select CompanyName From Company Where Id=a.CompanyId) As CompanyName,
	DepartmentId,
	(Select DName From Department Where Id=a.DepartmentId) As Department,
	UserName,
	(case '+convert(varchar,@bz)+' when 1 then  ename+''(''+isnull((Select DName From Department Where Id=a.DepartmentId),'''')+'')'' else '''' end) bz,
	EName,
	StateId,
	(Select Title From Assets_States Where Id=a.StateId) As StateName,
	Prices,
	DJUserName,
	DJDate,
	GGXH,
	GZDate,
	IsNULL(IsApplyBf,0) AS IsApplyBf,
	ANumber,
	IsNull(LyDate,''1900-01-01'') as LyDate,
	isnull(typeid,0) as typeid,
	(case isnull(typeid,0) when 1 then ''固定公用类'' when 2 then ''分配使用类'' when 3 then ''耗材低值类''  end) as typename
	From Assets a
	 '+@str+'
	)	
	Select * From list,(Select Count(Id) AS cid FROM list
	) AS n Where Row between '+convert(varchar(10),@StartIndex)+' and '+convert(varchar(10),@EndIndex)+'
	'
	EXEC (@strsql)
	--PRINT (@strsql)
	--PRINT(@str)
	RETURN
